﻿Option Explicit On

'*//Performance
'*// http://www.vbforums.com/archive/index.php/t-612740.html
Imports System.Reflection
Imports System.Globalization

Public Class clsTransactionList
    Inherits Windows.Forms.DataGridView

    Private m_Connectionstring As String
    Private m_AccountID As Integer

    Private Sub MakeGridDoubleBuffered(ByVal dgv As DataGridView)
        Try
            Dim dgvType As Type = dgv.[GetType]()
            Dim pi As PropertyInfo = dgvType.GetProperty("DoubleBuffered", BindingFlags.Instance Or BindingFlags.NonPublic)
            pi.SetValue(dgv, True, Nothing)
        Catch ex As Exception
            mdlLOGGING.fLOG("clsTransactionList => MakeGridDoubleBuffered: " & ex.Message.ToString, 99)
        End Try
    End Sub

    Public WriteOnly Property ConnectionString As String
        Set(value As String)
            m_Connectionstring = value
        End Set
    End Property

    Public WriteOnly Property AccountID As Integer
        Set(value As Integer)
            m_AccountID = value

            FillGrid()

        End Set
    End Property

    Private Function fGetSQL() As String
        '*// Table layout
        Return "SELECT C1.TRANSID," _
                                & " C1.TRANSACTIONNUMBER," _
                                & " C1.TRANSDATE," _
                                & " CASE WHEN C1.TRANSCODE = 'Transfer'" _
                                        & " THEN (CASE WHEN C1.ACCOUNTID = " & m_AccountID & " THEN 'Transfer to ' || ATO1.ACCOUNTNAME ELSE 'Transfer from ' || A1.ACCOUNTNAME END)" _
                                            & " ELSE P1.PAYEENAME END AS PAYEE_ACCOUNT," _
                                & " (CAT1.CATEGNAME || ':' || SUB1.SUBCATEGNAME) AS CATEGORIES," _
                                & " C1.STATUS," _
                                & " CASE WHEN C1.TRANSCODE = 'Withdrawal' THEN C1.TRANSAMOUNT" _
                                        & " ELSE (CASE WHEN C1.TRANSCODE = 'Transfer' AND C1.ACCOUNTID = " & m_AccountID & " THEN C1.TRANSAMOUNT ELSE 0 END)" _
                                            & " END AS WITHDRAWAL," _
                                & " CASE WHEN C1.TRANSCODE = 'Deposit' THEN C1.TRANSAMOUNT" _
                                        & " ELSE (CASE WHEN C1.TRANSCODE = 'Transfer' AND C1.TOACCOUNTID = " & m_AccountID & " THEN C1.TOTRANSAMOUNT ELSE 0 END)" _
                                            & "END AS DEPOSIT," _
                                & " 0 AS TOTAL, C1.NOTES" _
                        & " FROM CHECKINGACCOUNT_V1 C1" _
                                & " LEFT JOIN PAYEE_V1 P1" _
                                    & " ON C1.PAYEEID = P1.PAYEEID" _
                                & " LEFT JOIN CATEGORY_V1 CAT1" _
                                    & " ON C1.CATEGID = CAT1.CATEGID" _
                                & " LEFT JOIN SUBCATEGORY_V1 SUB1" _
                                    & " ON C1.SUBCATEGID = SUB1.SUBCATEGID" _
                                & " LEFT JOIN ACCOUNTLIST_V1 A1" _
                                    & " ON C1.ACCOUNTID = A1.ACCOUNTID" _
                                & " LEFT JOIN ACCOUNTLIST_V1 ATO1" _
                                    & " ON C1.TOACCOUNTID = ATO1.ACCOUNTID" _
                        & " WHERE (C1.ACCOUNTID = " & m_AccountID _
                                & " OR C1.TOACCOUNTID = " & m_AccountID _
                                & " )" _
                        & " ORDER BY C1.TRANSDATE"
    End Function

    Public Sub New()

        '*//Performance
        MakeGridDoubleBuffered(Me)

        '*// Dafault Values
        m_AccountID = -1

        '*// Setup Grid
        With Me
            .Columns.Clear()
            .Visible = True
            .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.None
            .AutoSize = False
            .SelectionMode = DataGridViewSelectionMode.FullRowSelect
            .CellBorderStyle = DataGridViewCellBorderStyle.SunkenHorizontal
            '.CellBorderStyle = DataGridViewCellBorderStyle.RaisedHorizontal
            .BorderStyle = Windows.Forms.BorderStyle.FixedSingle

            .RowHeadersVisible = False
            .ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.DisableResizing
            .MultiSelect = True

            .Columns.Add("TransID", "TransID")
            .Columns.Add("Number", "Number")
            .Columns.Add("Date", "Date")
            .Columns.Add("Payee", "Payee")
            .Columns.Add("Category", "Category")
            .Columns.Add("Status", "Status")
            .Columns.Add("Withdrawal", "Withdrawal")
            .Columns.Add("Deposit", "Deposit")
            .Columns.Add("Total", "Total")
            .Columns.Add("Comments", "Comments")

            .Columns(0).Visible = False '*// TransID
            .Columns(0).Width = 50 '*// TransID
            .Columns(1).Width = 50 '*// Number
            .Columns(2).Width = 80 '*// Date
            .Columns(3).Width = 180 '*// Payee
            .Columns(4).Width = 180 '*// Category
            .Columns(5).Width = 20 '*// Status
            .Columns(6).Width = 70 '*// Withdrawal
            .Columns(6).DefaultCellStyle.Format = "#,#0.00"
            .Columns(6).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
            .Columns(7).Width = 70 '*// Deposit
            .Columns(7).DefaultCellStyle.Format = "#,#0.00"
            .Columns(7).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
            .Columns(8).Width = 90 '*// Total
            .Columns(8).DefaultCellStyle.Format = "#,#0.00"
            .Columns(8).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
            .Columns(9).Width = 200 '*// Comments
        End With


    End Sub

    '** method to fill the grid
    Private Sub FillGrid()
        Dim cn As New SQLite.SQLiteConnection
        Dim da As New SQLite.SQLiteDataAdapter
        Dim ds As New DataSet

        Dim i, j As Integer

        Try

            Me.SuspendLayout()
            Me.Cursor = Cursors.WaitCursor

            With Me
                .Rows.Clear()
            End With

            Application.DoEvents()

            If m_Connectionstring <> "" And Not m_Connectionstring Is Nothing Then
                cn.ConnectionString = m_Connectionstring
                cn.Open()

                da = New SQLite.SQLiteDataAdapter(fGetSQL, cn)
                da.Fill(ds, "FillGrid")

                '*// Is there something to do? 
                If ds.Tables(0).Rows.Count > 0 Then
                    Me.Rows.Add(ds.Tables(0).Rows.Count)

                    '*// Add Rows
                    For i = 0 To ds.Tables(0).Rows.Count - 1

                        For j = 0 To Me.ColumnCount - 1

                            If Me.Columns(j).Name <> "Total" Then
                                Me.Rows(i).Cells(j).Value = ds.Tables(0)(i).Item(j)
                            Else
                                '*// running Total
                                If i > 0 Then
                                    Me.Rows(i).Cells(j).Value = (-Me.Rows(i).Cells(j - 2).Value + Me.Rows(i).Cells(j - 1).Value) + Me.Rows(i - 1).Cells(j).Value
                                Else
                                    Me.Rows(i).Cells(j).Value = (-Me.Rows(i).Cells(j - 2).Value + Me.Rows(i).Cells(j - 1).Value)
                                End If
                            End If

                        Next j
                    Next i


                End If

                cn.Close()
            End If


        Catch ex As Exception
            mdlLOGGING.fLOG("ucMygrid => FillGrid: " & ex.Message.ToString, 99)
        Finally

            cn = Nothing

            Me.ResumeLayout()
            Me.Cursor = Cursors.Default

        End Try

    End Sub

    Private Sub fTransEdit(ByVal TransID As Integer)
        Dim frmTEdit As New frmTransactionEdit

        frmTEdit.OpenTransactionEdit(TransID)

    End Sub

    Private Sub clsTransactionList_CellDoubleClick(sender As Object, e As System.Windows.Forms.DataGridViewCellEventArgs) Handles Me.CellDoubleClick

        If Not (Me.CurrentRow Is Nothing) Then
            If Me.CurrentRow.Index >= 0 Then
                fTransEdit(Me.Item(0, Me.CurrentRow.Index).Value)
            End If
        End If
    End Sub

    Private Sub clsTransactionList_KeyDown(sender As Object, e As System.Windows.Forms.KeyEventArgs) Handles Me.KeyDown

        If e.KeyCode = Keys.Enter Then
            fTransEdit(Me.Item(0, Me.CurrentRow.Index).Value)
            e.Handled = True
        End If
    End Sub
End Class
